<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class engineer_dashboard_model extends CI_Model
{
	
	public function __construct()
	{
		parent::__construct();

	}
	
public function get_image($id)
{

$query = $this->db->get_where('user_image', array('user_id' => $id) );
return $query;


}	
public function update_image($id,$image)
{
$insert=array('user_id'=>$id,
               'name' =>$image,);
			 	$this->db->update('user_image',$insert,array('user_id'=>$id));
	

}
public function insert_image($id)
{
$data=$this->db->get_where('user_image', array('user_id' =>$id))->num_rows();
if($data==0)
{
$insert= array('user_id'=>$id,);
$this->db->insert('user_image',$insert);

}	
else{ }



}

public function get_detail_repair($id)
{ 
$sql = "SELECT case.id, case.case_no, case.status_id,users.username\n"
    . "FROM `case` \n"
    . "JOIN users ON case.engineer_id=users.id\n"
	 . "JOIN repair ON case.id=repair.case_id\n"
    . "WHERE YEAR( repair.assigned_date ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( repair.assigned_date ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( assigned_date ) = DATE( NOW( ) ) \n"
	 . "AND USERS.ID =$id\n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();
	return $data;  
}
public function get_detail_diagnosis($id)
{ 
$sql = "SELECT case.id, case.case_no, case.status_id,users.username\n"
    . "FROM `case` \n"
    . "JOIN users ON case.engineer_id=users.id\n"
	 . "JOIN diagnosis ON case.id=diagnosis.case_id\n"
    . "WHERE YEAR( diagnosis.date ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( diagnosis.date ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( diagnosis.date ) = DATE( NOW( ) ) \n"
	 . "AND USERS.ID =$id\n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();
	return $data;  
}
public function get_nonwarranty_diagnosis($id)
{ 
$sql = "SELECT nonwarranty_case.id, nonwarranty_case.case_no, nonwarranty_case.status_id,users.username\n"
    . "FROM `nonwarranty_case` \n"
    . "JOIN users ON nonwarranty_case.engineer_id=users.id\n"
	 . "JOIN nonwarranty_diagnosis ON nonwarranty_diagnosis.case_id=nonwarranty_case.id\n"
    . "WHERE YEAR( nonwarranty_diagnosis.DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( nonwarranty_diagnosis.DATE ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( nonwarranty_diagnosis.DATE ) = DATE( NOW( ) ) \n"
	 . "AND USERS.ID =$id\n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();

	return $data;  
	

}
public function get_nonwarranty_repair($id)
{ 
$sql = "SELECT nonwarranty_case.id, nonwarranty_case.case_no, nonwarranty_case.status_id,users.username\n"
    . "FROM `nonwarranty_case` \n"
    . "JOIN users ON nonwarranty_case.engineer_id=users.id\n"
	  . "JOIN nonwarranty_repair ON nonwarranty_repair.case_id=nonwarranty_case.id\n"
    . "WHERE YEAR( nonwarranty_repair.assigned_date ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( nonwarranty_repair.assigned_date ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( nonwarranty_repair.assigned_date ) = DATE( NOW( ) ) \n"
	 . "AND USERS.ID =$id\n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();

	return $data;  
	

}					
public function get_month_caseid($id)
{
$sql = "SELECT case.id\n"
    . "FROM `case` \n"
	. "JOIN users ON case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND USERS.ID=$id \n"
    . "LIMIT 0 , 30";
$data=count($this->db->query( $sql)->result());
$sql1 = "SELECT nonwarranty_case.id\n"
    . "FROM `nonwarranty_case` \n"
	. "JOIN users ON nonwarranty_case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND USERS.ID=$id \n"
    . "LIMIT 0 , 30";
$data1=count($this->db->query( $sql1)->result());
return $data+$data1;
}	
public function get_month_diagnosis($id)
{
$sql = "SELECT diagnosis.id\n"
    . "FROM `diagnosis` \n"
	. "JOIN users ON diagnosis.enginer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND USERS.ID=$id \n"
    . "LIMIT 0 , 30";
$data=count($this->db->query( $sql)->result());
$sql1 = "SELECT nonwarranty_diagnosis.id\n"
    . "FROM `nonwarranty_diagnosis` \n"
	. "JOIN users ON nonwarranty_diagnosis.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND USERS.ID=$id \n"
    . "LIMIT 0 , 30";
$data1=count($this->db->query( $sql1)->result());
return $data+$data1;


}	
public function get_month_repair($id)
{
$sql = "SELECT repair.id\n"
    . "FROM `repair` \n"
	. "JOIN users ON repair.engineer_id=users.id\n"
    . "WHERE YEAR( ASSIGNED_DATE) = YEAR( NOW( ) ) \n"
    . "AND MONTH( ASSIGNED_DATE ) = MONTH( NOW( ) ) \n"
	. "AND repair.engineer_id=$id \n";
 
$data=count($this->db->query( $sql)->result());
$sql1 = "SELECT nonwarranty_repair.id\n"
    . "FROM `nonwarranty_repair` \n"
	. "JOIN users ON nonwarranty_repair.engineer_id=users.id\n"
    . "WHERE YEAR( ASSIGNED_DATE) = YEAR( NOW( ) ) \n"
    . "AND MONTH( ASSIGNED_DATE ) = MONTH( NOW( ) ) \n"
	. "AND nonwarranty_repair.engineer_id=$id \n";
 
$data1=count($this->db->query( $sql1)->result());
return $data+$data1;
}	
public function update_profile($insert,$id)
{
$this->db->update('users',$insert,array('id'=>$id));

}	
public function get_warranty($id)
{
$sql = "SELECT case.id\n"
    . "FROM `case` \n"
	. "JOIN users ON case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND case.engineer_id=$id \n";
 
$data=count($this->db->query( $sql)->result());
return $data;


}
public function get_nonwarranty($id)
{
$sql = "SELECT nonwarranty_case.id\n"
    . "FROM `nonwarranty_case` \n"
	. "JOIN users ON nonwarranty_case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	. "AND nonwarranty_case.engineer_id=$id \n";
   

$data=count($this->db->query( $sql)->result());
return $data;
}	
public function get_diagnosis($id)
{
$data=count($this->db->get_where('diagnosis', array('enginer_id' =>$id,'status_id'=>1,))->result());
$data1=count($this->db->get_where('nonwarranty_diagnosis', array('engineer_id' =>$id,'status_id'=>1,))->result());
echo $data+$data1;
}
public function get_repair($id)
{
$data=count($this->db->get_where('repair', array('engineer_id' =>$id,'status_id'=>5,))->result());
$data1=count($this->db->get_where('nonwarranty_repair', array('engineer_id' =>$id,'status_id'=>6,))->result());
echo $data+$data1;
}
public function get_profile($id)
{
$data=$this->db->get_where('users', array('id' =>$id));
return $data;

}	
	





	
	}?>